package XMLtest.dbtest;

import XMLtest.stu.Student;

import java.sql.*;
import java.util.ArrayList;

public class DBTest{
    public final static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 数据库驱动
    public final static String url = "jdbc:sqlserver://localhost:1433;DataBaseName=xmltest"; // url
    public final static String dbName = "sa"; // 数据库用户名
    public final static String dbPass = "1483851874"; // 数据库密码

    /**
     * mysql的驱动8.0

     public final static String driver = "com.mysql.cj.jdbc.Driver";
     public final static String url = "jdbc:mysql://localhost:3306/bbs?serverTimezone=UTC&characterEncoding=utf-8";
     public final static String dbName = "root";
     public final static String dbPass = "********";
     */


    /**
     * 得到数据库连接
     *
     * @throws ClassNotFoundException
     * @throws SQLException
     * @return 数据库连接
     */
    public Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName(driver); // 注册驱动
        Connection conn = DriverManager.getConnection(url, dbName, dbPass); // 获得数据库连接
        return conn; // 返回连接
    }
    /**
     * 释放资源
     *
     * @param conn
     *            数据库连接
     * @param pstmt
     *            PreparedStatement对象
     * @param rs
     *            结果集
     */
    public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        /* 如果rs不空，关闭rs */
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        /* 如果pstmt不空，关闭pstmt */
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        /* 如果conn不空，关闭conn */
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 执行SQL语句，可以进行增、删、改的操作，不能执行查询
     *
     * @param *sql
     *            预编译的 SQL 语句
     * @param param
     *            预编译的 SQL 语句中的‘？’参数的字符串数组
     * @return 影响的条数
     */
    public int executeSQL(String preparedSql, String[] param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int num = 0;

        /* 处理SQL,执行SQL */
        try {
            conn = getConn(); // 得到数据库连接
            pstmt = conn.prepareStatement(preparedSql); // 得到PreparedStatement对象
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstmt.setString(i + 1, param[i]); // 为预编译sql设置参数
                }
            }
            num = pstmt.executeUpdate(); // 执行SQL语句
        } catch (ClassNotFoundException e) {
            e.printStackTrace(); // 处理ClassNotFoundException异常
        } catch (SQLException e) {
            e.printStackTrace(); // 处理SQLException异常
        } finally {
            closeAll(conn, pstmt, null); // 释放资源
        }
        return num;
    }


    public ArrayList<Student> queryAll(String sql){
        Student s =null;
        ArrayList<Student> students = new ArrayList<Student>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConn(); // 得到数据库连接
            pstmt = conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                String name = rs.getString("sname");
                String sno = rs.getString("sno");
                String sex = rs.getString("sex");

                s = new Student(name,sno,sex);
                students.add(s);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(conn,pstmt,rs);

        }
        return students;
        }







}
